package com.example.yan.excel_split.controller;

import cn.hutool.json.JSONArray;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

import java.util.zip.ZipEntry;
import java.util.zip.ZipOutputStream;

//和平大悦城
@Controller
@Slf4j
@RequestMapping("/excel1")
public class UserController_zip_1 {

    private final static Logger logger = LoggerFactory.getLogger(UserController_zip.class);

    @RequestMapping("/heping")
    public String abc() {
        return "file_zip1";
    }


    @RequestMapping(value = "/file", method = RequestMethod.POST)
    @ResponseBody
    public Map<String, Object> file(@RequestParam(value = "file") MultipartFile file, HttpServletResponse response) throws IOException {
        // 返回一个layui可识别的返回值 先默认成功吧 后面需要处理上传失败或者处理失败的情况
        //      retrue resultMap;
        Map map = new HashMap<String, Object>();
        map.put("code", 0);// 0-表示成功
        map.put("msg", "上传成功");
        map.put("count", 1);


        Map<String, Object> resultMap = new HashMap<>();

        resultMap.put("code", 0);// 0-表示成功
        resultMap.put("msg", "上传成功");
        resultMap.put("count", 1);

        log.info("开始上传附件");
        //默认UTF-8编码，可以在构造中传入第二个参数做为编码
        ExcelReader reader = null;
        try {
            //解析导入的文件内容
            reader = ExcelUtil.getReader(file.getInputStream());
            List<List<Object>> list = reader.read();

            String header = null;
            for (List<Object> objects : list) {
                // 迭代删除空单元格
                Iterator<Object> it = objects.iterator();
                while (it.hasNext()) {
                    Object cellValue = it.next();
                    if (cellValue == null || StringUtils.isBlank(cellValue.toString())) {
                        it.remove();
                    }
                }

                String merchantCode = JSON.toJSONString(objects);

                if (merchantCode.contains("商户编号") && merchantCode.contains("商户地址")) {
                    System.out.println("merchantCode==>" + merchantCode);
                    //把所有的中括号取消
                    String str = merchantCode.substring(merchantCode.indexOf("[") + 1, merchantCode.lastIndexOf("]"));
                    String st = "商户/部门编号:" + str.substring(12, 26);
                    header = st;
                }

            }

            // 我们想要解析出来的表头
            String[] keyArr = new String[]{"终端号", "发卡行", "卡种", "序列号", "交易日期", "交易时间", "交易类型", "授权号", "交易金额", "小费", "分期期数", "银行手续费", "DCC返还手续费", "划账金额", "凭证号", "批次号", "POS交易序号",
                    "结算账号", "订单号", "柜台编号", "系统参考号", "持卡人姓名", "付款凭证号", "备注1", "备注2"};
            JSONArray dataList = new JSONArray();
            for (List<Object> aLineList : list) {
                if (aLineList.size() < keyArr.length)// 数据不满足表头长度的不要，说明该行数据不符合我们想要的格式
                    continue;
                Map<String, Object> hashMap = new HashMap<>();
                for (int j = 0; j < keyArr.length; j++) {
                    if (j < aLineList.size()) {
                        Object property = aLineList.get(j);
                        hashMap.put(keyArr[j], property);
                    }
                }
                dataList.add(hashMap);
            }
            System.out.println("解析之后所有符合条件的数据列表===" + dataList);
            // 终端编号的数组，最终得到一个去重的编号组

            List<String> noList = new ArrayList<>();
            for (Object objects : dataList) {
                JSONObject jsonObject = JSON.parseObject(objects.toString());
                String no = jsonObject.getString("终端号");
                if (StringUtils.isNotBlank(no)) {
                    // 去除表头，只要终端编号
                    if (!no.equals("终端号")) {
                        noList.add(no);
                    }
                }
            }
            double totalNew = 0;
            double totalNewB = 0;
            double totalNewC = 0;
            BigDecimal totaA = new BigDecimal("0");
            BigDecimal totaB = new BigDecimal("0");
            BigDecimal totaC = new BigDecimal("0");
            BigDecimal total1 = new BigDecimal("0");
            for (Object objects1 : dataList) {
                Map jsonObject = JSON.parseObject(objects1.toString());

                String jylx = jsonObject.get("交易类型").toString();
                switch (jylx) {

                    case "消费":
                        String amount = jsonObject.get("交易金额").toString();
                        totaA = total1.add(new BigDecimal(amount));
                        System.out.println("totaA==>" + totaA);
                        totalNew += Double.parseDouble(amount);
                        totaA = new BigDecimal(totalNew);
                        break;
                    case "银联二维码消费(被扫)":
                        String amount1 = jsonObject.get("交易金额").toString();
                        totalNewB += Double.parseDouble(amount1);
                        totaB = new BigDecimal(totalNewB);
                        break;
                    case "外卡消费":
                        String amount2 = jsonObject.get("交易金额").toString();
                        totalNewC += Double.parseDouble(amount2);
                        totaC = new BigDecimal(totalNewC);
                        break;
                }


            }
            // 3、处理数据之后，生成转化后的字节流
            List<ByteArrayOutputStream> bosList = new ArrayList<>();// 保存字节流数据
            List<String> excelName = new ArrayList<>();// 保存单个excel的文件名

            System.out.println("-----" + totalNew);

            Integer i = 1;
            // 编号去重
            if (noList.size() > 0) {
                List<String> newNoList = new ArrayList<>(new TreeSet<>(noList));
                if (newNoList.size() > 0) {
                    System.out.println("去重后的编号列表===" + newNoList);
                    List<Map> allList = new ArrayList<>();
                    List<Map> allListB = new ArrayList<>();
                    List<Map> allListC = new ArrayList<>();
                    List<Map> allListD = new ArrayList<>();
                    // 要把原来的数据根据编号进行分组过滤


                    List<Map> objListA = new ArrayList<>();
                    List<Map> objListB = new ArrayList<>();
                    List<Map> objListC = new ArrayList<>();
                    List<Map> objListD = new ArrayList<>();

                    for (String no : newNoList) {
                        List<Map> objList = new ArrayList<>();
                        List<Map> objList1 = new ArrayList<>();
                        List<Map> objList2 = new ArrayList<>();
                        List<Map> objList3 = new ArrayList<>();

                        // 合计值
                        BigDecimal total = new BigDecimal("0");
                        BigDecimal totalB = new BigDecimal("0");
                        BigDecimal totalC = new BigDecimal("0");
                        BigDecimal totalD = new BigDecimal("0");
                        // 序号
                        int num = 0;
                        int num1 = 0;
                        int num2 = 0;
                        int num3 = 0;
                        for (Object objects : dataList) {
                            //   Map jsonObject = JSON.parseObject(objects.toString());
                            Map jsonObject = JSON.parseObject(objects.toString());

                            Map row1 = new LinkedHashMap();
                            Map row2 = new LinkedHashMap();
                            Map row3 = new LinkedHashMap();
                            Map row4 = new LinkedHashMap();
                            //    Map<String, Object> row1 = new LinkedHashMap<>();
//                        Map<String, Object> row11 = new LinkedHashMap<>();
//                        Map<String, Object> row2 = new LinkedHashMap<>();
//                        Map<String, Object> row3 = new LinkedHashMap<>();

                            String terNumber = jsonObject.get("终端号").toString();
                            String isBank = jsonObject.get("发卡行").toString();
                            String tranAmout = jsonObject.get("交易金额").toString();
                            String tradeType = jsonObject.get("交易类型").toString();
                            String carsds = jsonObject.get("卡种").toString();
                            String cardNumber = jsonObject.get("序列号").toString();
                            String tradeDate = jsonObject.get("交易日期").toString();
                            String tradingHour = jsonObject.get("交易时间").toString();
                            String authCode = jsonObject.get("授权号").toString();
                            String tip = jsonObject.get("小费").toString();
                            String inPeriods = jsonObject.get("分期期数").toString();
                            String bankCharges = jsonObject.get("银行手续费").toString();
                            String refundComm = jsonObject.get("DCC返还手续费").toString();
                            String arrivalAmout = jsonObject.get("划账金额").toString();
                            String voucherNumber = jsonObject.get("凭证号").toString();
                            String bathNumber = jsonObject.get("批次号").toString();
                            String tranNumber = jsonObject.get("POS交易序号").toString();
                            String settAccount = jsonObject.get("结算账号").toString();
                            String orderNumber = jsonObject.get("订单号").toString();
                            String counterNumber = jsonObject.get("柜台编号").toString();
                            String systemNumber = jsonObject.get("系统参考号").toString();
                            String cardName = jsonObject.get("持卡人姓名").toString();
                            String PayMent = jsonObject.get("付款凭证号").toString();


                            if (terNumber.equals(no)) {
                                String amount = jsonObject.get("交易金额").toString();
                                switch (tradeType) {
                                    case "消费":
                                    case "消费撤销":
                                    case "联机退货":
                                        num++;
                                        if (!terNumber.contains("终端号")) {
                                            row1.put("序号", num);
                                            row1.put("终端号", terNumber);
                                            row1.put("发卡行", isBank);
                                            row1.put("交易金额", tranAmout);
                                            row1.put("交易类型", tradeType);
                                            row1.put("卡种", carsds);
                                            row1.put("卡号-序列号", cardNumber);
                                            row1.put("交易日期", tradeDate);
                                            row1.put("交易时间", tradingHour);
                                            row1.put("授权号", authCode);
                                            row1.put("小费", tip);
                                            row1.put("分期期数", inPeriods);
                                            row1.put("银行手续费", bankCharges);
                                            row1.put("DCC返还手续费", refundComm);
                                            row1.put("划账金额", arrivalAmout);
                                            row1.put("凭证号", voucherNumber);
                                            row1.put("批次号", bathNumber);
                                            row1.put("POS交易序号", tranNumber);
                                            row1.put("结算账号", settAccount);
                                            row1.put("订单号", orderNumber);
                                            row1.put("柜台编号", counterNumber);
                                            row1.put("系统参考号", systemNumber);
                                            row1.put("持卡人姓名", cardName);
                                            row1.put("付款凭证号", PayMent);

                                        }
                                        total = total.add(new BigDecimal(amount));
                                        objList.add(row1);
                                        break;

                                    /*case "银联二维码消费(被扫)":
                                    case "银联二维码退货":
                                        //row2.put("序号", num);
                                        num2++;
                                        if (!terNumber.contains("终端号")) {
                                            row2.put("序号", num2);
                                            row2.put("终端号", terNumber);
                                            row2.put("发卡行", isBank);
                                            row2.put("交易金额", tranAmout);
                                            row2.put("交易类型", tradeType);
                                            row2.put("卡种", carsds);
                                            row2.put("卡号-序列号", cardNumber);
                                            row2.put("交易日期", tradeDate);
                                            row2.put("交易时间", tradingHour);
                                            row2.put("授权号", authCode);
                                            row2.put("小费", tip);
                                            row2.put("分期期数", inPeriods);
                                            row2.put("银行手续费", bankCharges);
                                            row2.put("DCC返还手续费", refundComm);
                                            row2.put("划账金额", arrivalAmout);
                                            row2.put("凭证号", voucherNumber);
                                            row2.put("批次号", bathNumber);
                                            row2.put("POS交易序号", tranNumber);
                                            row2.put("结算账号", settAccount);
                                            row2.put("订单号", orderNumber);
                                            row2.put("柜台编号", counterNumber);
                                            row2.put("系统参考号", systemNumber);
                                            row2.put("持卡人姓名", cardName);
                                            row2.put("付款凭证号", PayMent);
                                        }
                                        totalB = totalB.add(new BigDecimal(amount));
                                        objList1.add(row2);
                                        break;*/

                                    case "外卡消费":
                                        num1++;
                                        //  row3.put("序号", num);
                                        if (!terNumber.contains("终端号")) {
                                            row3.put("序号", num1);
                                            row3.put("终端号", terNumber);
                                            row3.put("发卡行", isBank);
                                            row3.put("交易金额", tranAmout);
                                            row3.put("交易类型", tradeType);
                                            row3.put("卡种", carsds);
                                            row3.put("卡号-序列号", cardNumber);
                                            row3.put("交易日期", tradeDate);
                                            row3.put("交易时间", tradingHour);
                                            row3.put("授权号", authCode);
                                            row3.put("小费", tip);
                                            row3.put("分期期数", inPeriods);
                                            row3.put("银行手续费", bankCharges);
                                            row3.put("DCC返还手续费", refundComm);
                                            row3.put("划账金额", arrivalAmout);
                                            row3.put("凭证号", voucherNumber);
                                            row3.put("批次号", bathNumber);
                                            row3.put("POS交易序号", tranNumber);
                                            row3.put("结算账号", settAccount);
                                            row3.put("订单号", orderNumber);
                                            row3.put("柜台编号", counterNumber);
                                            row3.put("系统参考号", systemNumber);
                                            row3.put("持卡人姓名", cardName);
                                            row3.put("付款凭证号", PayMent);
                                        }
                                        totalC = totalC.add(new BigDecimal(amount));
                                        objList2.add(row3);
                                        break;

                                    case "银联二维码消费(被扫)":
                                    case "银联二维码退货":
                                    case "支付宝支付":
                                    case "微信支付":
                                    case "微信退款":
                                    case "支付宝退款":
                                    case "龙支付借记卡支付":
                                    case "龙支付贷记卡支付":
                                    case "龙支付退款":

                                        num3++;
                                        //  row3.put("序号", num);
                                        if (!terNumber.contains("终端号")) {
                                            row4.put("序号", num3);
                                            row4.put("终端号", terNumber);
                                            row4.put("发卡行", isBank);
                                            row4.put("交易金额", tranAmout);
                                            row4.put("交易类型", tradeType);
                                            row4.put("卡种", carsds);
                                            row4.put("卡号-序列号", cardNumber);
                                            row4.put("交易日期", tradeDate);
                                            row4.put("交易时间", tradingHour);
                                            row4.put("授权号", authCode);
                                            row4.put("小费", tip);
                                            row4.put("分期期数", inPeriods);
                                            row4.put("银行手续费", bankCharges);
                                            row4.put("DCC返还手续费", refundComm);
                                            row4.put("划账金额", arrivalAmout);
                                            row4.put("凭证号", voucherNumber);
                                            row4.put("批次号", bathNumber);
                                            row4.put("POS交易序号", tranNumber);
                                            row4.put("结算账号", settAccount);
                                            row4.put("订单号", orderNumber);
                                            row4.put("柜台编号", counterNumber);
                                            row4.put("系统参考号", systemNumber);
                                            row4.put("持卡人姓名", cardName);
                                            row4.put("付款凭证号", PayMent);
                                        }
                                        totalD = totalD.add(new BigDecimal(amount));
                                        objList3.add(row4);
                                        break;
                                }

                                //     objList.add(row1);
                                // 计算合计

                                // 序号
                                i++;

                            }
                        }

                        if (objList.size() > 0) {
//                        Map totalMap = new HashMap<>();
//                        totalMap.put("序号", "合计");
//                        totalMap.put("终端号", "");
//                        totalMap.put("发卡行", "");
//                        totalMap.put("交易金额", total);
                            //   objList.add(totalMap);
                            allList.addAll(objList);
                        }


                        if (objList1.size() > 0) {
//                        Map totalMap = new HashMap<>();
//                        totalMap.put("序号", "合计");
//                        totalMap.put("终端号", "");
//                        totalMap.put("发卡行", "");
//                        totalMap.put("交易金额", totalB);
                            // objListB.add(totalMap);
                            allListB.addAll(objList1);
                        }

                        if (objList2.size() > 0) {
//                        Map totalMap = new HashMap<>();
//                        totalMap.put("序号", "合计");
//                        totalMap.put("终端号", "");
//                        totalMap.put("发卡行", "");
//                        totalMap.put("交易金额", totalC);
                            //  objListC.add(totalMap);
                            allListC.addAll(objList2);
                        }


                        if (objList3.size() > 0) {
//                        Map totalMap = new HashMap<>();
//                        totalMap.put("序号", "合计");
//                        totalMap.put("终端号", "");
//                        totalMap.put("发卡行", "");
//                        totalMap.put("交易金额", totalC);
                            //  objListC.add(totalMap);
                            allListD.addAll(objList3);
                        }


                    }

                    if (allList.size() > 0) {
//                    Map totalMap = new HashMap<>();
//                    totalMap.put("序号", "总计合约");
//                    totalMap.put("终端号", "");
//                    totalMap.put("发卡行", "");
//                    totalMap.put("交易金额", totaA);
                        // objListA.add(totalMap);
                        //   allList.addAll(objListA);
                        //     exportExcel1(allList, "建设银行国内卡", header);
                        bosList.add(createExcel(allList, header));
                        excelName.add("建设银行国内卡" + ".xls");
                    }
              //      if (allListB.size() > 0) {
//                    Map totalMap = new HashMap<>();
//                    totalMap.put("序号", "总计合约");
//                    totalMap.put("终端号", "");
//                    totalMap.put("发卡行", "");
//                    totalMap.put("交易金额", totaB);
                        // allListB.add(totalMap);
                        //allListB.addAll(objListB);
                        //    System.out.println("根据编号分组之后的数据" + allList);
                        // 导出
                        // exportExcel1(allListB, "建设银行银联二维码", header);
//                        bosList.add(createExcel(allListB, header));
//                        excelName.add("建设银行银联二维码" + ".xls");
//                    }
                    if (allListC.size() > 0) {
//                    Map totalMap = new HashMap<>();
//                    totalMap.put("序号", "总计合约");
//                    totalMap.put("终端号", "");
//                    totalMap.put("发卡行", "");
//                    totalMap.put("交易金额", totaC);
                        //allListC.add(totalMap);
                        //  allListC.addAll(objListC);
                        // 导出
                        //exportExcel1(allListC, "建设银行国外卡", header);
                        bosList.add(createExcel(allListC, header));
                        excelName.add("建设银行国外卡" + ".xls");
                    }

                    if (allListD.size() > 0) {
//                    Map totalMap = new HashMap<>();
//                    totalMap.put("序号", "总计合约");
//                    totalMap.put("终端号", "");
//                    totalMap.put("发卡行", "");
//                    totalMap.put("交易金额", totaC);
                        //allListC.add(totalMap);
                        // allListD.addAll(objListD);
                        // 导出
                        //exportExcel1(allListC, "建设银行国外卡", header);
                        bosList.add(createExcel(allListD, header));
                        excelName.add("建设银行聚合支付" + ".xls");
                    }

                }


                // 5、开始导出ZIP 创建HttpServerResponse的输出流
                OutputStream out = response.getOutputStream();
                // 创建要写入的文件
                File outFile = new File("excel.zip");
                // 通过ZipOutputStream定义要写入的对象
                ZipOutputStream zos = new ZipOutputStream(new FileOutputStream(outFile));
                // 将处理好的excel数据流写入到zip流
                writeZos(bosList, zos, excelName);
                zos.close();
                // 设置请求头 定义返回类型
                response.setContentType("text/html; charset=UTF-8");
                response.setContentType("application/octet-stream");
                response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("excel.zip", "UTF-8"));
                // 创建写入流
                BufferedInputStream bis = new BufferedInputStream(new FileInputStream("excel.zip"));
                // 定义byte，长度就是要转成zip文件的byte长度，避免浪费资源
                byte[] buffer = new byte[bis.available()];
                bis.read(buffer);
                out.flush();
                out.write(buffer);

                return null;
            }
        } catch (IOException e) {
            e.printStackTrace();
            logger.error("=======" + e);
        }
        return resultMap;
    }

    public ByteArrayOutputStream createExcel(List<Map> list, String header) {
        ExcelWriter writer = ExcelUtil.getWriter();
        writer.merge(3, header);
        writer.write(list, true);
        // 写入流
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        writer.flush(out);
        writer.close();
        return out;
    }

    private static ByteArrayOutputStream exportExcel1(List<Map> list, String fileName, String header) {
        Date date = new Date();

        String path = "home/bank/bankdata";
        String path1 = " E:\\桌面\\新建文件夹(4)\\";
        //ExcelWriter writer = ExcelUtil.getWriter(path1 + exportDate(date) + fileName + ".xlsx");
        ExcelWriter writer = ExcelUtil.getWriter();
        writer.setOnlyAlias(true);
// 合并单元格后的标题行，使用默认标题样式
        writer.merge(3, header);
// 一次性写出内容，使用默认样式，强制输出标题
        writer.write(list, true);

        // 写入流
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        writer.flush(out);
        // 关闭writer，释放内存
        writer.close();
        System.out.println("执行完了导出在" + path1);
        return out;
    }


    //获取当下时间
    private static String exportDate(Date date) {

        SimpleDateFormat sdf = new SimpleDateFormat();// 格式化时间
        sdf.applyPattern("yyyyMMdd");
        System.out.println("现在时间：" + sdf.format(date)); //
        return sdf.format(date);
    }


    /**
     * 把生成的excel的结果流，放到zip流中，以便导出
     *
     * @param bosList   带数据的字节流合集
     * @param zos       zip流
     * @param excelName 文件名合集
     * @throws IOException
     */
    public void writeZos(List<ByteArrayOutputStream> bosList, ZipOutputStream zos, List<String> excelName) throws IOException {
        for (int i = 0; i < bosList.size(); i++) {
            //将多个excel都转成字节流写入
            zos.putNextEntry(new ZipEntry(excelName.get(i)));
            byte[] excelStream = bosList.get(i).toByteArray();
            zos.write(excelStream);
            //记得关闭
            zos.closeEntry();
        }
    }

}
